package com.ruyiadmin.springboot.domain.dto.system;

import cn.hutool.core.io.FileUtil;
import cn.hutool.core.io.file.FileNameUtil;
import cn.hutool.core.util.NumberUtil;
import com.baomidou.mybatisplus.annotation.*;
import com.ruyiadmin.springboot.common.core.system.enums.CellDataType;
import com.ruyiadmin.springboot.common.exceptions.RuYiAdminCustomException;
import com.ruyiadmin.springboot.common.utils.core.RuYiDateUtil;
import com.ruyiadmin.springboot.common.utils.core.RuYiFileUtil;
import com.ruyiadmin.springboot.common.utils.core.RuYiPoiUtil;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.UUID;
import java.util.regex.Pattern;

/**
 * 导入配置DTO模型
 *
 * @author RuYiAdmin
 * @since 2022-08-13
 */
@Data
public class ImportConfigDTO {
    @ApiModelProperty("主键")
    @TableId("ID")
    private String id;

    @ApiModelProperty("配置名称")
    @TableField("CONFIG_NAME")
    private String configName;

    @ApiModelProperty("起始行")
    @TableField("START_ROW")
    private Integer startRow;

    @ApiModelProperty("起始列")
    @TableField("START_COLUMN")
    private Integer startColumn;

    @ApiModelProperty("工作簿索引列表")
    @TableField("WORKSHEET_INDEXES")
    private String worksheetIndexes;

    @TableField("SERIAL_NUMBER")
    private Integer serialNumber;

    @ApiModelProperty("备注")
    @TableField("REMARK")
    private String remark;

    @ApiModelProperty("标志位")
    @TableField(value = "ISDEL", fill = FieldFill.INSERT)
    @TableLogic
    private Integer isdel;

    @ApiModelProperty("创建人")
    @TableField(value = "CREATOR", fill = FieldFill.INSERT)
    private String creator;

    @ApiModelProperty("创建时间")
    @TableField(value = "CREATE_TIME", fill = FieldFill.INSERT)
    private LocalDateTime createTime;

    @ApiModelProperty("修改人")
    @TableField(value = "MODIFIER", fill = FieldFill.INSERT_UPDATE)
    private String modifier;

    @ApiModelProperty("修改时间")
    @TableField(value = "MODIFY_TIME", fill = FieldFill.INSERT_UPDATE)
    private LocalDateTime modifyTime;

    @ApiModelProperty("版本号")
    @TableField(value = "VERSION_ID", fill = FieldFill.INSERT_UPDATE)
    @Version    // 乐观锁注解
    private String versionId;

    @TableField(exist = false)
    private String excelPath;

    private List<ImportConfigDetailDTO> children;

    //region 数据合法性校验

    public int validationDetecting() throws IOException {
        int errorCount = 0;

        if (StringUtils.isEmpty(this.getExcelPath())) {
            throw new RuYiAdminCustomException("file can not be empty");
        }

        FileInputStream fileInputStream = new FileInputStream(this.getExcelPath());
        Workbook workbook = new HSSFWorkbook(fileInputStream);

        if (!StringUtils.isEmpty(this.getWorksheetIndexes())) {
            String[] array = this.getWorksheetIndexes().split(",");
            for (String worksheetIndex : array) {
                Sheet worksheet = workbook.getSheetAt(Integer.parseInt(worksheetIndex));
                errorCount += this.detectingWorksheet(worksheet);
            }
        } else {
            Sheet worksheet = workbook.getSheetAt(0);
            errorCount += this.detectingWorksheet(worksheet);
        }

        String id = UUID.randomUUID().toString();
        String path = this.getExcelPath();
        this.setExcelPath(RuYiFileUtil.copyFile(path, id));

        FileOutputStream fileOutputStream = new FileOutputStream(this.getExcelPath());
        workbook.write(fileOutputStream);

        workbook.close();
        fileOutputStream.close();
        fileInputStream.close();

        return errorCount;
    }

    //endregion

    //region 模型私有方法

    //region 校验工作簿

    /**
     * 校验工作簿
     *
     * @param worksheet 工作簿
     * @return 错误数量
     */
    private int detectingWorksheet(Sheet worksheet) {
        int errorCount = 0;

        for (ImportConfigDetailDTO item : this.getChildren()) {

            //region 校验必填项

            if (item.getRequired() != null && item.getRequired().equals(1)) {
                List<String> cols = Arrays.asList(item.getCells().split(","));
                for (int i = this.getStartRow(); i <= worksheet.getLastRowNum(); i++) {
                    for (int j = this.getStartColumn(); j < worksheet.getRow(this.getStartRow()).getLastCellNum(); j++) {
                        int finalJ = j;
                        if (cols.stream().anyMatch(t -> t.equals(String.valueOf(finalJ)))) {
                            String value = RuYiPoiUtil.getCellValue(worksheet.getRow(i).getCell(j));
                            if (StringUtils.isEmpty(value)) {
                                errorCount++;
                                RuYiPoiUtil.setCellComment(worksheet, i, j, "单元格的值不能为空！");
                            }
                        }
                    }
                }
            }

            //endregion

            //region 按数据类型校验

            if (item.getDataType().equals(CellDataType.DECIMAL.ordinal())) {
                errorCount += this.decimalValidation(worksheet, item);
            } else if (item.getDataType().equals(CellDataType.INTEGER.ordinal())) {
                errorCount += this.integerValidation(worksheet, item);
            } else if (item.getDataType().equals(CellDataType.TEXT.ordinal())) {
                errorCount += this.textValidation(worksheet, item);
            } else if (item.getDataType().equals(CellDataType.DATE.ordinal())) {
                errorCount += this.dateValidation(worksheet, item);
            } else if (item.getDataType().equals(CellDataType.DATETIME.ordinal())) {
                errorCount += this.dateTimeValidation(worksheet, item);
            }

            //endregion

        }

        return errorCount;
    }
    //endregion

    //region 校验小数问题

    /**
     * 校验小数问题
     *
     * @param worksheet    工作簿
     * @param detailConfig 配置明细
     * @return 错误数量
     */
    private int decimalValidation(Sheet worksheet, ImportConfigDetailDTO detailConfig) {
        int errorCount = 0;

        //region 校验小数、最大值、最小值、小数位数

        List<String> cols = Arrays.asList(detailConfig.getCells().split(","));

        for (int i = this.getStartRow(); i <= worksheet.getLastRowNum(); i++) {
            for (int j = this.getStartColumn(); j < worksheet.getRow(this.getStartRow()).getLastCellNum(); j++) {
                String value = RuYiPoiUtil.getCellValue(worksheet.getRow(i).getCell(j));
                //校验小数、最大值、最小值
                int finalJ = j;
                if (cols.stream().anyMatch(t -> t.equals(String.valueOf(finalJ)))
                        && !StringUtils.isEmpty(value)) {
                    //region 校验小数

                    if (!NumberUtil.isNumber(value)) {
                        errorCount++;
                        RuYiPoiUtil.setCellComment(worksheet, i, j, "单元格的值不是数字！");
                    }

                    //endregion

                    //region 校验最大值

                    if (NumberUtil.isNumber(value) && detailConfig.getMaxValue() != null
                            && Double.parseDouble(value) > detailConfig.getMaxValue()) {
                        errorCount++;
                        RuYiPoiUtil.setCellComment(worksheet, i, j, "单元格的值不能大于最大值！");
                    }

                    //endregion

                    //region 校验最小值

                    if (NumberUtil.isNumber(value) && detailConfig.getMinValue() != null
                            && Double.parseDouble(value) < detailConfig.getMinValue()) {
                        errorCount++;
                        RuYiPoiUtil.setCellComment(worksheet, i, j, "单元格的值不能小于最小值！");
                    }

                    //endregion
                }

                //校验小数位数
                if (cols.stream().anyMatch(t -> t.equals(String.valueOf(finalJ)))
                        && !StringUtils.isEmpty(value) && NumberUtil.isNumber(value)
                        && detailConfig.getDecimalLimit() != null
                        && value.contains(".") && value.split("\\.")[1].length()
                        > detailConfig.getDecimalLimit()) {
                    errorCount++;
                    RuYiPoiUtil.setCellComment(worksheet, i, j, "小数位数超限！");
                }
            }
        }

        //endregion

        return errorCount;
    }
    //endregion

    //region 校验整数问题

    /**
     * 校验整数问题
     *
     * @param worksheet    工作簿
     * @param detailConfig 配置明细
     * @return 错误数量
     */
    private int integerValidation(Sheet worksheet, ImportConfigDetailDTO detailConfig) {
        int errorCount = 0;

        //region 校验整数、最大值、最小值、枚举

        List<String> cols = Arrays.asList(detailConfig.getCells().split(","));
        List<String> enumCols = new ArrayList<>();
        if (!StringUtils.isEmpty(detailConfig.getTextEnum())) {
            enumCols = Arrays.asList(detailConfig.getTextEnum().split(","));
        }

        for (int i = this.getStartRow(); i <= worksheet.getLastRowNum(); i++) {
            for (int j = this.getStartColumn(); j < worksheet.getRow(this.getStartRow()).getLastCellNum(); j++) {
                String value = RuYiPoiUtil.getCellValue(worksheet.getRow(i).getCell(j));
                //校验整数、最大值、最小值
                int finalJ = j;
                if (cols.stream().anyMatch(t -> t.equals(String.valueOf(finalJ)))
                        && !StringUtils.isEmpty(value)) {
                    //region 校验整数

                    if (!NumberUtil.isNumber(value)) {
                        errorCount++;
                        RuYiPoiUtil.setCellComment(worksheet, i, j, "单元格的值不是数字！");
                    }

                    if (!NumberUtil.isInteger(value)) {
                        errorCount++;
                        RuYiPoiUtil.setCellComment(worksheet, i, j, "单元格的值不是整数！");
                    }

                    //endregion

                    //region 校验最大值

                    if (NumberUtil.isNumber(value) && detailConfig.getMaxValue() != null
                            && Double.parseDouble(value) > detailConfig.getMaxValue()) {
                        errorCount++;
                        RuYiPoiUtil.setCellComment(worksheet, i, j, "单元格的值不能大于最大值！");
                    }

                    //endregion

                    //region 校验最小值

                    if (NumberUtil.isNumber(value) && detailConfig.getMinValue() != null
                            && Double.parseDouble(value) < detailConfig.getMinValue()) {
                        errorCount++;
                        RuYiPoiUtil.setCellComment(worksheet, i, j, "单元格的值不能小于最小值！");
                    }

                    //endregion
                }

                //校验枚举
                if (cols.stream().anyMatch(t -> t.equals(String.valueOf(finalJ)))
                        && !StringUtils.isEmpty(value) && enumCols.size() > 0
                        && enumCols.stream().noneMatch(t -> t.equals(value))) {
                    errorCount++;
                    RuYiPoiUtil.setCellComment(worksheet, i, j, "单元格的值不在枚举列表中！");
                }
            }
        }

        //endregion

        return errorCount;
    }

    //endregion

    //region 校验文本错误

    /**
     * 校验文本错误
     *
     * @param worksheet    工作簿
     * @param detailConfig 配置明显
     * @return 错误数量
     */
    private int textValidation(Sheet worksheet, ImportConfigDetailDTO detailConfig) {
        int errorCount = 0;

        //region 校验枚举问题

        if (!StringUtils.isEmpty(detailConfig.getTextEnum())) {
            List<String> cols = Arrays.asList(detailConfig.getCells().split(","));
            List<String> enumCols = Arrays.asList(detailConfig.getTextEnum().split(","));

            for (int i = this.getStartRow(); i <= worksheet.getLastRowNum(); i++) {
                for (int j = this.getStartColumn(); j < worksheet.getRow(this.getStartRow()).getLastCellNum(); j++) {
                    String value = RuYiPoiUtil.getCellValue(worksheet.getRow(i).getCell(j));
                    //校验枚举
                    int finalJ = j;
                    if (cols.stream().anyMatch(t -> t.equals(String.valueOf(finalJ)))
                            && !StringUtils.isEmpty(value) && enumCols.size() > 0
                            && enumCols.stream().noneMatch(t -> t.equals(value))) {
                        errorCount++;
                        RuYiPoiUtil.setCellComment(worksheet, i, j, "单元格的值不在枚举列表中！");
                    }
                }
            }
        }

        //endregion

        //region 校验正则表达式

        if (!StringUtils.isEmpty(detailConfig.getExtend1())) {
            List<String> cols = Arrays.asList(detailConfig.getCells().split(","));

            for (int i = this.getStartRow(); i <= worksheet.getLastRowNum(); i++) {
                for (int j = this.getStartColumn(); j < worksheet.getRow(this.getStartRow()).getLastCellNum(); j++) {
                    String value = RuYiPoiUtil.getCellValue(worksheet.getRow(i).getCell(j));
                    try {
                        //校验正则表达式
                        int finalJ = j;
                        if (cols.stream().anyMatch(t -> t.equals(String.valueOf(finalJ)))
                                && !StringUtils.isEmpty(value)
                                && !Pattern.compile(detailConfig.getExtend1()).matcher(value).matches()) {
                            errorCount++;
                            RuYiPoiUtil.setCellComment(worksheet, i, j, "单元格的值不满足正则表达式！");
                        }
                    } catch (Exception ex) {
                        errorCount++;
                        RuYiPoiUtil.setCellComment(worksheet, i, j, ex.getMessage());
                    }
                }
            }
        }

        //endregion

        return errorCount;
    }
    //endregion

    //region 校验日期问题

    /**
     * 校验日期问题
     *
     * @param worksheet    工作簿
     * @param detailConfig 配置明显
     * @return 错误数量
     */
    private int dateValidation(Sheet worksheet, ImportConfigDetailDTO detailConfig) {
        int errorCount = 0;

        //region 校验日期

        List<String> cols = Arrays.asList(detailConfig.getCells().split(","));

        for (int i = this.getStartRow(); i <= worksheet.getLastRowNum(); i++) {
            for (int j = this.getStartColumn(); j < worksheet.getRow(this.getStartRow()).getLastCellNum(); j++) {
                String value = RuYiPoiUtil.getCellValue(worksheet.getRow(i).getCell(j));
                //校验日期
                int finalJ = j;
                if (cols.stream().anyMatch(t -> t.equals(String.valueOf(finalJ)))
                        && !StringUtils.isEmpty(value) && RuYiDateUtil.isDate(value)) {
                    errorCount++;
                    RuYiPoiUtil.setCellComment(worksheet, i, j, "单元格的值不是日期！");
                }
            }
        }

        //endregion

        return errorCount;
    }

    //endregion

    //region 校验时间问题

    /**
     * 校验时间问题
     *
     * @param worksheet    工作簿
     * @param detailConfig 配置明显
     * @return 错误数量
     */
    private int dateTimeValidation(Sheet worksheet, ImportConfigDetailDTO detailConfig) {
        int errorCount = 0;

        //region 校验时间

        List<String> cols = Arrays.asList(detailConfig.getCells().split(","));

        for (int i = this.getStartRow(); i <= worksheet.getLastRowNum(); i++) {
            for (int j = this.getStartColumn(); j < worksheet.getRow(this.getStartRow()).getLastCellNum(); j++) {
                String value = RuYiPoiUtil.getCellValue(worksheet.getRow(i).getCell(j));
                //校验时间
                int finalJ = j;
                if (cols.stream().anyMatch(t -> t.equals(String.valueOf(finalJ)))
                        && !StringUtils.isEmpty(value) && !RuYiDateUtil.isDateTime(value)) {
                    errorCount++;
                    RuYiPoiUtil.setCellComment(worksheet, i, j, "单元格的值不是时间！");
                }
            }
        }

        //endregion

        return errorCount;
    }

    //endregion

    //endregion
}
